package com.xiongkw.dbutil;

import org.apache.commons.io.FileUtils;

import java.io.File;
import java.sql.*;
import java.util.*;
import java.util.Date;

public class GenSqlMapMysql {
    private String tablename = "";//表名
    private String entityPackageName = "com.user.entity";
    private String mapperPackageName = "com.user.mapper";
    private String modelName;

    private List<Map<String,Object>> tableDescription;
    //数据库连接
    private  String URL ="jdbc:mysql://111.231.157.164:3306/uranus?useUnicode=true&characterEncoding=UTF-8";
    private  String NAME = "uranus";
    private  String PASS = "123456";
    private  String DRIVER ="com.mysql.jdbc.Driver";

    private static final String authorName = "xiongkw";

    public GenSqlMapMysql(String entityPackageName,String mapperPackageName){
        this.tablename = tablename;
        this.entityPackageName = entityPackageName;
        this.mapperPackageName = mapperPackageName;
    }

    public void setModelName(){
        this.modelName = this.initcap(this.dbNameToJavaName(this.tablename));
    }

    public void setDbUrl(String dbUrl){
        this.URL = dbUrl;
    }

    public void setDbUserName(String dbUserName){
        this.NAME = dbUserName;
    }

    public void setDbPassword(String dbPassword){
        this.PASS = dbPassword;
    }

    public void setTablename(String tablename){
        this.tablename = tablename;
        this.loadTableDescription();
        this.setModelName();
    }

    private void loadTableDescription(){
        String sql = "show full columns from " + this.tablename;
        try{
            this.tableDescription = this.executeSelectSql(sql);
        }catch (Exception e){
            e.printStackTrace();
        }
    }
    private Connection getConnection() throws Exception{
        Class.forName(DRIVER);
        return DriverManager.getConnection(URL,NAME,PASS);
    }

    private List<Map<String,Object>> executeSelectSql(String sql) throws Exception{
        Connection con = getConnection();
        List<Map<String,Object>> resList;
        ResultSet rs =null;
        try {
            PreparedStatement ps = con.prepareStatement(sql);
            ps.execute();
            rs = ps.getResultSet();
            resList =  this.resultSetToList(rs);
            con.close();

        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return  resList;
    }

    private List<Map<String,Object>> resultSetToList(ResultSet rs) throws java.sql.SQLException {
        if (rs == null)
            return Collections.EMPTY_LIST;
        ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数
        List<Map<String,Object>> list = new ArrayList();
        Map<String,Object> rowData;
        while (rs.next()) {
            rowData = new HashMap(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            list.add(rowData);
            System.out.println("list:" + list.toString());
        }
        return list;
    }


    /**
     * 功能：将输入字符串的首字母改成大写
     * @param str
     * @return
     */
    private String initcap(String str) {

        char[] ch = str.toCharArray();
        if(ch[0] >= 'a' && ch[0] <= 'z'){
            ch[0] = (char)(ch[0] - 32);
        }
        return new String(ch);
    }

    /**
     * 将数据库风格的名称改为java风格的名称  xxx_yyy_mm  xxxYyyMm
     * @return
     */
    private String dbNameToJavaName(String str){
        String strarray [] = str.split("_");
        StringBuilder sb = new StringBuilder(strarray[0]);
        for(int i = 1;i<strarray.length;i++){
            sb.append(this.initcap(strarray[i]));
        }
        return sb.toString();
    }

    private String sqlType2JavaType(String sqlType) {
        if(sqlType.equalsIgnoreCase("bit")){
            return "boolean";
        }else if(sqlType.equalsIgnoreCase("tinyint")){
            return "Integer";
        }else if(sqlType.equalsIgnoreCase("smallint")){
            return "Integer";
        }else if(sqlType.equalsIgnoreCase("int")){
            return "Integer";
        }else if(sqlType.equalsIgnoreCase("bigint")){
            return "Long";
        }else if(sqlType.equalsIgnoreCase("float")){
            return "Float";
        }else if(sqlType.equalsIgnoreCase("decimal") || sqlType.equalsIgnoreCase("numeric")
                || sqlType.equalsIgnoreCase("real") || sqlType.equalsIgnoreCase("money")
                || sqlType.equalsIgnoreCase("smallmoney")){
            return "Double";
        }else if(sqlType.equalsIgnoreCase("varchar") || sqlType.equalsIgnoreCase("char")
                || sqlType.equalsIgnoreCase("nvarchar") || sqlType.equalsIgnoreCase("nchar")
                || sqlType.equalsIgnoreCase("text")){
            return "String";
        }else if(sqlType.equalsIgnoreCase("datetime")){
            return "Date";
        }else if(sqlType.equalsIgnoreCase("image")){
            return "Blod";
        }else if(sqlType.equalsIgnoreCase("timestamp")) {
            return "Date";
        }

        return null;
    }

    public String baseColumn() throws  Exception{
        StringBuilder sbColumns = new StringBuilder();
        for(Map<String,Object> m : this.tableDescription){
            sbColumns.append(m.get("COLUMN_NAME"))
                    .append(" "+this.dbNameToJavaName(m.get("COLUMN_NAME").toString()))
                    .append(",");
        }

        return "<sql id=\"BaseColumn\">\n"
                +sbColumns.substring(0,sbColumns.length()-1).toString()+"\n"
                +"</sql>\n";
    }

    public String insertSql() throws  Exception{
        String insertSql = "insert into " + tablename;
        StringBuilder sbColumns = new StringBuilder();
        StringBuilder sbValues = new StringBuilder();
        for(Map<String,Object> m : this.tableDescription){
            sbColumns.append(m.get("COLUMN_NAME")).append(",");
            sbValues.append("#{").append(this.dbNameToJavaName(m.get("COLUMN_NAME").toString())).append("},");
        }
        insertSql = insertSql +"("+sbColumns.substring(0,sbColumns.length()-1)+") values ("
                +sbValues.substring(0,sbValues.length()-1)+")";

        return "<insert id=\"insert\" parameterType=\""+this.entityPackageName+"."+this.modelName+"\">\n"
                +insertSql + "\n"
                +"</insert>";
    }

    public String deleteById(){
       return  "<delete id=\"deleteById\" parameterType=\"java.lang.Long\">\n"
        +"delete from "+ tablename +" where id =#{id}\n"
        +"</delete>\n";
    }

    public String updateSql(){
        String updateSql = "update " + tablename+"\n <trim prefix=\"set\" suffixOverrides=\",\"> \n";
        String columName = "";
        String columnValue="";
        for(Map<String,Object> m : this.tableDescription){
            columName = m.get("COLUMN_NAME").toString();
            columnValue = "#{"+this.dbNameToJavaName(m.get("COLUMN_NAME").toString())+"}";
            updateSql =updateSql+"<if test=\""+this.dbNameToJavaName(m.get("COLUMN_NAME").toString())+" != null\">"+columName+"="+columnValue+",</if>\n";
        }
        updateSql += "</trim>\n  WHERE id=#{id}";
        return "<update id=\"update\" parameterType=\""+this.entityPackageName+"."+this.modelName+"\">\n"
        +updateSql+"\n"
        +"</update>\n";
    }

    public String selectAllByCondition(){
        String selectSql = "SELECT <include refid=\"BaseColumn\"></include> from "+this.tablename+"\n"
                + "<trim prefix=\"where\" prefixOverrides=\"and|or\">";

        String columName = "";
        String columnValue="";
        for(Map<String,Object> m : this.tableDescription){
            columName = m.get("COLUMN_NAME").toString();
            columnValue = "#{"+this.dbNameToJavaName(m.get("COLUMN_NAME").toString())+"}";
            selectSql =selectSql+"<if test=\""+this.dbNameToJavaName(m.get("COLUMN_NAME").toString())+" != null\"> and "+columName+"="+columnValue+"</if>\n";
        }
        selectSql +="</trim>";

        return "<select id=\"queryByCondition\" parameterType=\""+this.entityPackageName+"."+this.modelName+"\" resultType=\""+this.entityPackageName+"."+this.modelName+"\">\n"
        +selectSql +"\n"
        +"</select>\n";

    }

    public String selectById(){
        String  sql = "SELECT <include refid=\"BaseColumn\"></include> from "+this.tablename+"\n" +
                 "  where id = #{id}";
        return "<select id=\"getById\" parameterType=\"java.lang.Long\" resultType=\""+this.entityPackageName+"."+this.modelName+"\">\n"
                +sql +"\n"
                +"</select>\n";

    }

    public void sqlMapToFile() throws  Exception{
        StringBuilder sb = new StringBuilder();
        sb.append("<?xml version=\"1.0\" encoding=\"UTF-8\" ?>\n" +
                "<!DOCTYPE mapper\n" +
                "        PUBLIC \"-//mybatis.org//DTD Mapper 3.0//EN\"\n" +
                "        \"http://mybatis.org/dtd/mybatis-3-mapper.dtd\">\n" +
                "<mapper namespace=\""+this.mapperPackageName+"."+this.modelName+"Mapper\">\n\n");
        sb.append(this.baseColumn()+"\n\n")
                .append(this.insertSql()+"\n\n")
                .append(this.deleteById()+"\n\n")
                .append(this.updateSql()+"\n\n")
                .append(this.selectAllByCondition()+"\n\n")
                .append(this.selectById()+"\n\n");
        sb.append("</mapper>");
        File directory = new File("");
        String outputPath = directory.getAbsolutePath()+ "/src/main/java/"+this.mapperPackageName.replace(".", "/")+"/"+initcap(dbNameToJavaName(tablename)) + "Mapper.xml";
        System.out.println(outputPath);
        FileUtils.writeStringToFile(new File(outputPath), sb.toString(), "utf-8");
    }

    public void sqlMapperToFile() throws Exception{
        StringBuilder sb = new StringBuilder();
        sb.append("package "+this.mapperPackageName+";\n\n");
        sb.append("import "+this.entityPackageName+"."+this.modelName+";\n\n");
        sb.append("import java.util.List;\n\n");
        //注释部分
        sb.append("   /**\r\n");
        sb.append("    * "+tablename+" mapper\r\n");
        sb.append("    * "+new Date()+" "+this.authorName+"\r\n");
        sb.append("    */ \r\n");
        sb.append("public interface "+this.modelName+"Mapper {\n\n");
        sb.append("  Long insert("+this.modelName+" "+ this.dbNameToJavaName(this.tablename)+");\n\n");
        sb.append("  int deleteById(Long id);\n\n");
        sb.append("  int update("+this.modelName+" "+ this.dbNameToJavaName(this.tablename)+");\n\n");
        sb.append("  List<"+this.modelName+"> queryByCondition("+this.modelName+" "+ this.dbNameToJavaName(this.tablename)+");\n\n");
        sb.append("  "+this.modelName +" getById(Long id);\n\n");
        sb.append("}");

        File directory = new File("");
        String outputPath = directory.getAbsolutePath()+ "/src/main/java/"+this.mapperPackageName.replace(".", "/")+"/"+initcap(dbNameToJavaName(tablename)) + "Mapper.java";
        System.out.println(outputPath);
        FileUtils.writeStringToFile(new File(outputPath), sb.toString(), "utf-8");

    }

    public void entityToFile() throws  Exception{
        StringBuffer sb = new StringBuffer();

        sb.append("package " + this.entityPackageName.replace("main.java.", "") + ";\r\n");
        sb.append("\r\n");
        sb.append("import java.util.Date;\r\n");
        //注释部分
        sb.append("   /**\r\n");
        sb.append("    * "+tablename+" 实体类\r\n");
        sb.append("    * "+new Date()+" "+this.authorName+"\r\n");
        sb.append("    */ \r\n");
        //实体部分
        sb.append("\r\n\r\npublic class " + initcap(dbNameToJavaName(tablename)) + "{\r\n");

        for(Map<String,Object> m : this.tableDescription){
            sb.append("\t //@ApiModelProperty(notes=\""+m.get("COLUMN_COMMENT")+"\")\r\n");
            sb.append("\tprivate " + sqlType2JavaType(m.get("COLUMN_TYPE").toString().replaceAll("\\(.*\\)","")) + " " + dbNameToJavaName(m.get("COLUMN_NAME").toString()) + ";\r\n");
        }
        //processAllMethod(sb);//get set方法
        sb.append("}\r\n");

        File directory = new File("");
        String outputPath = directory.getAbsolutePath()+ "/src/main/java/"+this.entityPackageName.replace(".", "/")+"/"+initcap(dbNameToJavaName(tablename)) + ".java";
        System.out.println(outputPath);
        FileUtils.writeStringToFile(new File(outputPath), sb.toString(), "utf-8");
    }

    public void generateDbAccessFiles()throws  Exception{
        //生成entity
        this.entityToFile();
        //生成sqlmap
        this.sqlMapToFile();
        //生成mapper
        this.sqlMapperToFile();
    }
}
